﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Reflection;
using Dapper;
using Newtonsoft.Json;
using API_Service.DBHelper;

namespace API_Service.BizLogic.BasicData
{
    public class GroupKPNCodeService: BaseLogicService
    {        
        public GroupKPNCodeService() { }
        public ExecutionResult<dynamic> GetGroupKPNCfgList(string vGroupName =null,string vKPNType= null)
        {
            return this.QueryGroupCfgData(vGroupName, vKPNType);
        }
        public ExecutionResult<dynamic> AddGroupKPNCfg(dynamic vModel)
        {
            return this.InsertGroupPickupData(vModel);
        }
        public ExecutionResult<dynamic> UpdateGroupKPNCfg(dynamic vModel)
        {
            return this.UpdateGroupPickupData(vModel);
        }
        public ExecutionResult<dynamic> DeleteGroupKPNCfg(int vId)
        {
            return this.DeleteGroupPickupData(vId);
        }
        private ExecutionResult<dynamic> QueryGroupCfgData(string vGroupName = null, string vKPNCode=null)
        {
            var result = new ExecutionResult<dynamic>();
            string sql = @"SELECT [id],[group_name],[station_name],[kpn_code],[remarks]
                                                  ,[is_valid],[create_time],[create_user],[update_time],[update_user]
                                              FROM [dbo].[c_group_kpn_code_cfg_t] 
                                            WHERE 1=1 AND [is_valid]=@is_valid ";
            var dbParams = new DynamicParameters();
            if (!string.IsNullOrEmpty(vGroupName))
            {
                sql = sql + " AND [group_name] LIKE @group_name";
                dbParams.Add("@group_name", "%" + vGroupName + "%", DbType.String, ParameterDirection.Input);
            }
            if (!string.IsNullOrEmpty(vKPNCode))
            {
                sql = sql + " AND [kpn_code] LIKE @kpn_code ";
                dbParams.Add("@kpn_code", "%" + vKPNCode + "%", DbType.String, ParameterDirection.Input);
            }
          
            dbParams.Add("@is_valid", "Y", DbType.String, ParameterDirection.Input);
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    result.Datas = conn.Query<dynamic>(sql, dbParams).ToArray();
                    result.Message = "查询站点所需捡料信息成功。";
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "查询站点捡料信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> InsertGroupPickupData(dynamic vGroupCfg)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    var _groupCfg = JsonConvert.DeserializeObject<dynamic>(vGroupCfg); 

                    string _insertSql = @"INSERT INTO [dbo].[c_group_kpn_code_cfg_t]
                                                               ([group_name],[station_name],[kpn_code],[remarks],[is_valid],[create_time],[create_user])
                                                         VALUES(@group_name,@station_name,@kpn_code,@remarks,@is_valid,GETDATE(),@create_user)";
                    var dbParams = new DynamicParameters();
                    dbParams.Add("@group_name", (string)_groupCfg.group_name, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@station_name", (string)_groupCfg.station_name, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@kpn_code", (string)_groupCfg.kpn_code, DbType.String, ParameterDirection.Input);                    
                    dbParams.Add("@remarks", (string)_groupCfg.remarks, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@is_valid", "Y", DbType.String, ParameterDirection.Input);
                    dbParams.Add("@create_user", (string)_groupCfg.create_user, DbType.String, ParameterDirection.Input);

                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "新增站点捡料信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "新增站点捡料信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "新增站点捡料信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> UpdateGroupPickupData(dynamic vGroupCfg)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    var _groupCfg = JsonConvert.DeserializeObject<dynamic>(vGroupCfg);

                    string _updateSql = @"UPDATE [dbo].[c_group_kpn_code_cfg_t]
                                                       SET [station_name] = @station_name,[kpn_code] = @kpn_code,[remarks] = @remarks,
	                                                       [is_valid] = @is_valid,[update_time] = GETDATE(),[update_user] = @update_user
                                                     WHERE Id=@id";
                    var dbParams = new DynamicParameters();

                    dbParams.Add("@id", (Int32)_groupCfg.id, DbType.Int32, ParameterDirection.Input);
                    dbParams.Add("@station_name", (string)_groupCfg.station_name, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@kpn_code", (string)_groupCfg.kpn_code, DbType.String, ParameterDirection.Input);                    
                    dbParams.Add("@remarks", (string)_groupCfg.remarks, DbType.String, ParameterDirection.Input);
                    dbParams.Add("@is_valid", (string)_groupCfg.is_valid, DbType.String, ParameterDirection.Input);
                    //dbParams.Add("@update_time", vGroupCfg.update_time, DbType.DateTime, ParameterDirection.Input);
                    dbParams.Add("@update_user", (string)_groupCfg.update_user, DbType.String, ParameterDirection.Input);


                    result.ExecuteCount = conn.Execute(_updateSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "更新站点捡料信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "更新站点捡料信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "更新站点捡料信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }
        private ExecutionResult<dynamic> DeleteGroupPickupData(int vId)
        {
            var result = new ExecutionResult<dynamic>();
            using (IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    string _insertSql = @"DELETE FROM [dbo].[c_group_kpn_code_cfg_t] WHERE [id] = @id";
                    var dbParams = new DynamicParameters();

                    dbParams.Add("@id", vId, DbType.Int32, ParameterDirection.Input);

                    result.ExecuteCount = conn.Execute(_insertSql, dbParams);
                    if (result.ExecuteCount > 0)
                    {
                        result.Status = true;
                        result.Message = "删除站别捡料信息成功.";
                    }
                    else
                    {
                        result.Status = false;
                        result.Message = "删除站别捡料信息失败";
                    }
                    Logger.WriteDebug(MethodBase.GetCurrentMethod().Name + ": " + result.Message.ToString());
                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "删除站别捡料信息异常";
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }

        public ExecutionResult<dynamic> GetKPNCodeList()
        {
            ExecutionResult<dynamic> result;
            result = new ExecutionResult<dynamic>();
            string sql = "SELECT DISTINCT[part_code] FROM [dbo].[c_part_code_t] WHERE [is_valid]='Y'";
            using(IDbConnection conn = SqlCreator.Create())
            {
                try
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();

                    result.Datas = conn.Query(sql).ToArray();
                    result.Status = true;
                    result.Message = "获取材料Code别成功。";

                }
                catch (Exception ex)
                {
                    result.Status = false;
                    result.Message = "获取KPN Code异常，异常信息：" + ex.Message;
                    Logger.WriteError(MethodBase.GetCurrentMethod().Name, ex);
                }
            }

            return result;
        }
    }
}